﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data.SqlClient;

namespace Electure.DAL
{
    public class News: BaseModel
    {
        public string Name { get; set; }
        public DateTime CreateDate { get; set; }
        public DateTime EditDate { get; set; }
        public string Description { get; set; }
        public Attachment Image { get; set; }
    }

    public class NewsRepository: BaseRepository<News>
    {
        internal NewsRepository() { }

        protected override int create(News instance)
        {
            var param = new DynamicParameters();
            param.Add("Name", instance.Name);
            param.Add("Description", instance.Description);
            var sql = "INSERT INTO news(";
            if(instance.Image != null)
            {
                param.Add("ImageId", instance.Image.ID);
            }
            var fields = param.ParameterNames.ToList();
            fields.ForEach(item => sql += item + ",");
            sql = sql.Substring(0, sql.Length - 1) + ") VALUES (";
            fields.ForEach(item => sql += "@" + item + ",");
            sql = sql.Substring(0, sql.Length - 1) + ")";
            return connection.Execute(sql, param);
        }
        public override bool Update(News instance)
        {
            try
            {
                var param = new DynamicParameters();
                param.Add("Name", instance.Name);
                param.Add("Description", instance.Description);
                var sql = "UPDATE news SET ";
                if (instance.Image != null)
                {
                    param.Add("ImageId", instance.Image.ID);
                }
                var fields = param.ParameterNames.ToList();
                fields.ForEach(item => sql += string.Format("{0}=@{0},", item));
                param.Add("ID", instance.ID);
                sql += " editdate = getutcdate() WHERE id = @ID";

                return connection.Execute(sql, param) == 1;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public override bool Delete(int id)
        {
            return connection.Execute("DELETE FROM news WHERE ID = @id", new { id = id }) == 1;
        }
        public override List<News> GetAll()
        {
            return connection.Query<News, Attachment, News>("SELECT * FROM News n LEFT JOIN Attachment a ON n.ImageId = a.ID ORDER BY n.CreateDate DESC",
                (news, attachment) => { news.Image = attachment; return news; }).ToList();
        }
        public List<News> Search(string query)
        {
            List<KeyValuePair<int, News>> result = new List<KeyValuePair<int, News>>();
            var news = GetAll();

            var queryList = query.ToLower().Split(new char[] { ' ' });

            news.ForEach(item =>
            {
                int cnt = 0;

                cnt += item.Name.ToLower().Split(queryList, StringSplitOptions.None).Length - 1;
                cnt += item.Description.ToLower().Split(queryList, StringSplitOptions.None).Length - 1;

                if (cnt > 0)
                {
                    result.Add(new KeyValuePair<int, News>(cnt, item));
                }
            });

            return (from item in result
                    orderby item.Key descending
                    select item.Value)
                    .ToList();
        }
    }
}
